package com.gitee.huanminabc.jlambda_sql.jdbc;

import com.alibaba.fastjson.JSONObject;
import com.gitee.huanminabc.jcommon.base.DateUtil;
import com.gitee.huanminabc.jcommon.fastjson.FastJsonUtil;
import com.gitee.huanminabc.jcommon.model.DataKeyValue;
import com.gitee.huanminabc.jcommon.exception.CommonException;
import com.gitee.huanminabc.jcommon.reflect.AnnotationUtil;
import com.gitee.huanminabc.jcommon.reflect.FieldUtil;
import com.gitee.huanminabc.jcommon.str.StringUtil;
import com.gitee.huanminabc.jlambda_sql.IdType;
import com.gitee.huanminabc.jlambda_sql.annotation.TableField;
import com.gitee.huanminabc.jlambda_sql.annotation.TableId;
import com.gitee.huanminabc.jlambda_sql.annotation.TableName;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * 简要描述
 *
 * @Author: huanmin
 * @Date: 2025/4/5 11:58
 * @Version: 1.0
 * @Description: 文件作用详细描述....
 */
@Slf4j
public class JdbcCommand {

    public static boolean sqlLogPrintOpen = false;

    //打印执行sql的日志
    public static void sqlLogPrint(Integer affectedRows, String sql, Object... args) {
        if (sqlLogPrintOpen) {
            //格式和Mybatis一致
            log.info("==>  Preparing: \n{}\n", sql);
            StringBuilder sb = new StringBuilder();
            if (args != null && args.length > 0) {
                sb.append("==>  Parameters: ");
                for (int i = 0; i < args.length; i++) {
                    Object arg = args[i];
                    //如果是时间类型,那么转换为时间字符串
                    if (arg instanceof Date) {
                        arg = DateUtil.format((Date) arg);
                    }
                    sb.append(arg).append("(").append(arg.getClass().getSimpleName()).append(")");
                    if (i < args.length - 1) {
                        sb.append(", ");
                    }
                }
                sb.append("\n");
            }
            log.info(sb.toString());
            if (affectedRows != null) {
                log.info("==>  Affected rows: {}", affectedRows);
            }
        }
    }


    public static <T> T selectOne(String sql, Class<T> tClass, Object... args) {
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            T object = ResultSetUtil.getObject(resultSet, tClass);
            if (object != null) {
                sqlLogPrint(1, sql, args);
            } else {
                sqlLogPrint(0, sql, args);
            }
            return object;
        } catch (SQLException e) {
            throw new CommonException(e, "Error executing query: " + sql);
        } finally {
            JdbcManage.connectionClose(connection);

        }
    }

    public static <T> T selectById(Class<T> tClass, Serializable id) {
        String fieldNameId = getClassPkIdName(tClass);
        TableName annotation = getTableName(tClass);
        String sql = "select * from " + annotation.value() + " where " + fieldNameId + " = ?";
        return selectOne(sql, tClass, id);
    }

    public static <T> T selectByIds(Class<T> tClass, Collection<?> ids) {
        String fieldNameId = getClassPkIdName(tClass);
        TableName annotation = getTableName(tClass);
        CharSequence[] idsc = ids.stream().map(Object::toString).toArray(CharSequence[]::new);
        String sql = "select * from " + annotation.value() + " where " + fieldNameId + " in (" + String.join(",", idsc) + ")";
        return selectOne(sql, tClass);
    }

    public static <T> List<T> selectList(String sql, Class<T> tClass, Object... args) {
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            List<T> objectList = ResultSetUtil.getObjectList(resultSet, tClass);
            sqlLogPrint(objectList.size(), sql, args);
            return objectList;
        } catch (SQLException e) {
            throw new CommonException(e, "Error executing query: " + sql);
        } finally {
            JdbcManage.connectionClose(connection);

        }
    }

    public static Map<String, Object> selectMap(String sql, Object... args) {
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            Map<String, Object> map = ResultSetUtil.getMap(resultSet);
            sqlLogPrint(map.size(), sql, args);
            return map;
        } catch (SQLException e) {
            throw new CommonException(e, "Error executing query: " + sql);
        } finally {
            JdbcManage.connectionClose(connection);
        }
    }

    public static List<Map<String, Object>> selectMaps(String sql, Object... args) {
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            List<Map<String, Object>> listMap = ResultSetUtil.getListMap(resultSet);
            sqlLogPrint(listMap.size(), sql, args);
            return listMap;
        } catch (SQLException e) {
            throw new CommonException(e, "Error executing query: " + sql);
        } finally {
            JdbcManage.connectionClose(connection);
        }
    }


    public static boolean exists(String sql, Object... args) {
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            boolean next = resultSet.next();
            sqlLogPrint(1, sql, args);
            return next;
        } catch (SQLException e) {
            throw new CommonException(e, "Error executing query: " + sql);
        } finally {
            JdbcManage.connectionClose(connection);

        }
    }

    public static Object selectFirstItem(String sql, Object... args) {
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                Object object = resultSet.getObject(1);
                sqlLogPrint(1, sql, args);
                return object;
            }
            return 0;
        } catch (SQLException e) {
            throw new CommonException("Error executing query: " + sql, e);
        } finally {
            JdbcManage.connectionClose(connection);
        }
    }


    public static boolean insert(String sql, Object... args) {
        return execute(sql, args) > 0;
    }

    public static <T> boolean insertBatch(List<T> list) {
        if (list == null || list.isEmpty()) {
            throw new CommonException("list is empty");
        }
        Object entity = list.get(0);
        Class<?> tClass = entity.getClass();
        //先组装sql
        TableName annotation = tClass.getAnnotation(TableName.class);
        if (annotation == null) {
            throw new CommonException(tClass.getName() + " 没有找到@TableName注解");
        }
        StringBuilder sql = new StringBuilder("insert into " + annotation.value() + "(");
        StringBuilder values = new StringBuilder(" values (");
        DataKeyValue<Field, TableId> dataKeyValue = AnnotationUtil.getAnnotationsField(tClass, TableId.class);
        boolean idInput = false;
        if (dataKeyValue != null) {
            TableId fieldAnnotation = dataKeyValue.getValue();
            if (fieldAnnotation != null && fieldAnnotation.type() == IdType.INPUT) {
                String classId = getClassPkIdName(tClass);
                sql.append(classId).append(",");
                values.append("?").append(",");
                idInput = true;
            }
        }
        Map<Field, TableField> annotationsFieldMap = AnnotationUtil.getAnnotationsFieldMap(tClass, TableField.class);
        if (annotationsFieldMap.isEmpty()) {
            throw new CommonException(tClass.getName() + " 没有找到@TableField注解的字段");
        }
        for (Map.Entry<Field, TableField> entry : annotationsFieldMap.entrySet()) {
            Field field = entry.getKey();
            TableField tableField = entry.getValue();
            field.setAccessible(true);
            Object value = FieldUtil.getFieldValue(field, entity);
            if (value != null) {
                String name = tableField.value();
                if (StringUtil.isEmpty(name)) {
                    name = field.getName();
                }
                sql.append(name).append(",");
                values.append("?").append(",");
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        values.deleteCharAt(values.length() - 1);
        sql.append(")").append(values).append(")");
        //执行批量插入
        String finalSql = sql.toString();
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(finalSql)) {
            for (Object object : list) {
                int argIndex = 1;
                if (idInput) {
                    Object classIdValue = getClassPkIdValue(object);
                    if (classIdValue == null) {
                        throw new CommonException(object.getClass() + "主键值不能为空");
                    }
                    preparedStatement.setObject(1, classIdValue);
                    argIndex++;
                }

                //利用JSON解析, 这样可以走JSONWriter
                Map<String, Object> data = FastJsonUtil.toMap(object);
                for (Map.Entry<Field, TableField> entry : annotationsFieldMap.entrySet()) {
                    Field field = entry.getKey();
                    field.setAccessible(true);
                    Object value = data.get(field.getName());
                    if (value != null) {
                        preparedStatement.setObject(argIndex++, value);
                    }
                }
                preparedStatement.addBatch();
            }
            int[] result = preparedStatement.executeBatch();
            sqlLogPrint(result.length, finalSql);
            return result.length > 0;
        } catch (SQLException e) {
            throw new CommonException(e, "Error executing batch insert: " + sql);
        } finally {
            JdbcManage.connectionClose(connection);
        }
    }

    public static <T> boolean insert(T object) {
        Class<?> tClass = object.getClass();
        Map<Field, TableField> annotationsFieldMap = AnnotationUtil.getAnnotationsFieldMap(tClass, TableField.class);
        if (annotationsFieldMap.isEmpty()) {
            throw new CommonException(tClass.getName() + " 没有找到@TableField注解的字段");
        }
        TableName annotation = tClass.getAnnotation(TableName.class);
        if (annotation == null) {
            throw new CommonException(tClass.getName() + " 没有找到@TableName注解");
        }
        StringBuilder sql = new StringBuilder("insert into " + annotation.value() + "(");
        //如果不是自增那么就需要手动填充
        DataKeyValue<Field, TableId> dataKeyValue = AnnotationUtil.getAnnotationsField(tClass, TableId.class);
        boolean idInput = false;
        if (dataKeyValue != null) {
            TableId fieldAnnotation =dataKeyValue.getValue();
            if (fieldAnnotation != null && fieldAnnotation.type() == IdType.INPUT) {
                String classId = getClassPkIdName(tClass);
                sql.append(classId).append(",");
                idInput = true;
            }
        }

        StringBuilder values = new StringBuilder(" values (");
        List<Object> args = new ArrayList<>();
        if ( idInput) {
            values.append("?").append(",");
            Object classIdValue = getClassPkIdValue(object);
            if (classIdValue == null) {
                throw new CommonException(object.getClass() + "主键值不能为空");
            }
            args.add(classIdValue);
        }

        //利用JSON解析, 这样可以走JSONWriter
        Map<String, Object> data = FastJsonUtil.toMap(object);
        for (Map.Entry<Field, TableField> entry : annotationsFieldMap.entrySet()) {
            Field field = entry.getKey();
            TableField tableField = entry.getValue();
            field.setAccessible(true);
            Object value =data.get(field.getName());
            if (value != null) {
                String name = tableField.value();
                if (StringUtil.isEmpty(name)) {
                    name = field.getName();
                }
                sql.append(name).append(",");
                values.append("?").append(",");
                args.add(value);
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        values.deleteCharAt(values.length() - 1);
        sql.append(")").append(values).append(")");

        Object o = executeReturnId( sql.toString(), args.toArray());
        if (o == null) {
            return false;
        }
        //补充主键
        setClassIdValue(object, o);
        return true;
    }


    //insertOrUpdate
    public static <T> boolean insertOrUpdate(T object) {
        Object classIdValue = getClassPkIdValue(object);
        if (classIdValue == null) {
            return insert(object);
        } else {
            return updateById(object);
        }
    }


    public static <T> boolean deleteById(Class<T> tClass, Serializable id) {
        String fieldNameId = getClassPkIdName(tClass);
        TableName annotation = getTableName(tClass);
        String sql = "delete from " + annotation.value() + " where " + fieldNameId + " = " + id;
        return execute(sql) > 0;
    }


    public static <T> boolean deleteByIds(Class<T> tClass, Collection<?> ids) {
        String fieldNameId = getClassPkIdName(tClass);
        TableName annotation = getTableName(tClass);
        CharSequence[] idsc = ids.stream().map(Object::toString).toArray(CharSequence[]::new);
        String sql = "delete from " + annotation.value() + " where " + fieldNameId + " in (" + String.join(",", idsc) + ")";
        return execute(sql) > 0;
    }

    //updateById
    public static <T> boolean updateById(T object) {
        //利用JSON解析, 这样可以走JSONWriter
        Map<String, Object> data = FastJsonUtil.toMap(object);
        Class<?> tClass = object.getClass();
        Map<Field, TableField> annotationsFieldMap = AnnotationUtil.getAnnotationsFieldMap(tClass, TableField.class);
        if (annotationsFieldMap.isEmpty()) {
            throw new CommonException(tClass.getName() + " 没有找到@TableField注解的字段");
        }
        TableName tableName = getTableName(tClass);
        StringBuilder sql = new StringBuilder("update " + tableName.value() + " set ");
        String fieldNameId = getClassPkIdName(tClass);
        List<Object> args = new ArrayList<>();
        for (Map.Entry<Field, TableField> entry : annotationsFieldMap.entrySet()) {
            Field field = entry.getKey();
            TableField tableField = entry.getValue();
            field.setAccessible(true);
            Object value =  data.get(field.getName());
            if (value != null) {
                String name = tableField.value();
                if (StringUtil.isEmpty(name)) {
                    name = field.getName();
                }
                sql.append(name).append("=?,");
                args.add(value);
            }
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" where ").append(fieldNameId).append("=?");
        Object classIdValue = getClassPkIdValue(object);
        if (classIdValue == null) {
            throw new CommonException(object.getClass() + "主键值不能为空");
        }
        args.add(classIdValue);
        return execute(sql.toString(), args.toArray()) > 0;
    }


    //delete
    public static <T> boolean delete(String sql, Object... args) {
        return execute(sql, args) > 0;
    }

    //update
    public static boolean update(String sql, Object... args) {
        return execute(sql, args) > 0;
    }


    public static int execute(String sql, Object... args) {
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            int i = preparedStatement.executeUpdate();
            sqlLogPrint(i, sql, args);
            return i;
        } catch (SQLException e) {
            throw new CommonException(e);
        } finally {
            JdbcManage.connectionClose(connection);
        }
    }

    public static Object executeReturnId(String sql, Object... args) {
        Connection connection = getConnection();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            int i = preparedStatement.executeUpdate();
            sqlLogPrint(i, sql,args);
            if (i <= 0) {
                return null;
            }
            // 获取生成的主键
            try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    //通常是第一个生成的主键
                    return generatedKeys.getObject(1);
                }
            }
            return i;
        } catch (SQLException e) {
            throw new CommonException(e);
        } finally {
            JdbcManage.connectionClose(connection);
        }
    }

    //如果当前有事务那么就使用事务的连接
    public static Connection getConnection() {
        String transactionKey = TransactionManage.peekTransactionKey();
        if (transactionKey == null) {
            return getNewConnection();
        }
        Connection transactionConnection = TransactionManage.getTransactionConnection(transactionKey);
        if (transactionConnection == null) {
            throw new CommonException("No transaction connection found for key: " + transactionKey);
        }
        return transactionConnection;
    }

    public static Connection getNewConnection() {
        DataSource dataSource = JdbcManage.getDataSource();
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new CommonException(e, "Error getting connection");
        }
    }

    //获取主键的名称
    public static <T> String getClassPkIdName(Class<T> tClass) {
        DataKeyValue<Field, TableId> dataKeyValue = AnnotationUtil.getAnnotationsField(tClass, TableId.class);
        if (dataKeyValue == null) {
            throw new CommonException(tClass.getName() + " 没有找到@TableId注解的字段");
        }
        String fieldName = tClass.getName();
        TableId tableId = dataKeyValue.getValue();
        if (tableId != null && StringUtil.isNotEmpty(tableId.value())) {
            fieldName = tableId.value();
        }
        return fieldName;
    }


    //获取表名
    public static <T> TableName getTableName(Class<T> tClass) {
        TableName annotation = tClass.getAnnotation(TableName.class);
        if (annotation == null) {
            throw new CommonException(tClass.getName() + " 没有找到@TableName注解");
        }
        return annotation;
    }


    //获取主键的值
    public static <T> Object getClassPkIdValue(T tClass) {
        Class<?> aClass = tClass.getClass();
        DataKeyValue<Field, TableId> dataKeyValue = AnnotationUtil.getAnnotationsField(aClass, TableId.class);
        if (dataKeyValue == null) {
            throw new CommonException(aClass.getName() + " 没有找到@TableId注解的字段");
        }
        return FieldUtil.getFieldValue(dataKeyValue.getKey(), tClass);
    }

    //设置主键的值
    public static <T> void setClassIdValue(T target, Object value) {
        Class<?> aClass = target.getClass();
        DataKeyValue<Field, TableId> dataKeyValue = AnnotationUtil.getAnnotationsField(aClass, TableId.class);
        if (dataKeyValue == null) {
            throw new CommonException(aClass.getName() + " 没有找到@TableId注解的字段");
        }
        FieldUtil.setFieldValue(target, dataKeyValue.getKey(), value);
    }


}


